{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series([1,2,3,4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1\n1    2\n2    3\n3    4\ndtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n"
     ]
    }
   ],
   "source": [
    "print(s[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    False\n1    False\n2     True\n3     True\ndtype: bool\n"
     ]
    }
   ],
   "source": [
    "print(s > 2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2    3\n3    4\ndtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(s[s > 2])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    1\nb    2\nc    3\nd    4\ndtype: int64\n"
     ]
    }
   ],
   "source": [
    "d = {\"a\": 1, \"b\": 2, \"c\": 3, \"d\": 4}\n",
    "s2 = pd.Series(d)\n",
    "print(s2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  name  age\n0   张三   23\n1   李四   24\n2   王五   25\n"
     ]
    }
   ],
   "source": [
    "t = {\"name\": [\"张三\", \"李四\", \"王五\"], \"age\": [23, 24, 25]}\n",
    "df = pd.DataFrame(t)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    张三\n1    李四\n2    王五\nName: name, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df[\"name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  name  age\n2   王五   25\n"
     ]
    }
   ],
   "source": [
    "print(df[df[\"age\"] > 24])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.drop(\"name\",axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   age\n0   23\n1   24\n2   25\n"
     ]
    }
   ],
   "source": [
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  name  age\n0   张三   23\n2   王五   25\n"
     ]
    }
   ],
   "source": [
    "df2 = df.drop(1,axis=0)\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        age\ncount   3.0\nmean   24.0\nstd     1.0\nmin    23.0\n25%    23.5\n50%    24.0\n75%    24.5\nmax    25.0\n"
     ]
    }
   ],
   "source": [
    "print(df.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "24.0\n23\n25\n1.0\n"
     ]
    }
   ],
   "source": [
    "print(df[\"age\"].mean())\n",
    "print(df[\"age\"].min())\n",
    "print(df[\"age\"].max())\n",
    "print(df[\"age\"].std())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  name  age\n2   王五   25\n"
     ]
    }
   ],
   "source": [
    "# 统计年龄大于平均年龄的学生\n",
    "\n",
    "print(df[df[\"age\"] > df[\"age\"].mean()])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {\"x1\": [1, 2, 3, 4, 5, 6], \"x2\": [10, 20, 30, 40, 50, 60]}\n",
    "df = pd.DataFrame(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          x1        x2\nx1  1.000000 -0.629794\nx2 -0.629794  1.000000\n"
     ]
    }
   ],
   "source": [
    "print(df.corr())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [],
   "source": [
    "student = pd.read_csv(\"com/shujia/students.txt\"\n",
    "                      ,sep=\",\"\n",
    "                      ,encoding=\"utf-8\",header=None,names=[\"id\",\"name\",\"age\",\"gender\",\"clazz\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           id name  age gender clazz\n0  1500100001  施笑槐   22      女  文科六班\n1  1500100002  吕金鹏   24      男  文科六班\n2  1500100003  单乐蕊   22      女  理科六班\n3  1500100004  葛德曜   24      男  理科三班\n4  1500100005  宣谷芹   22      女  理科五班\n"
     ]
    }
   ],
   "source": [
    "print(student.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             id name  age gender clazz\n1    1500100002  吕金鹏   24      男  文科六班\n3    1500100004  葛德曜   24      男  理科三班\n5    1500100006  边昂雄   21      男  理科二班\n8    1500100009  沈德昌   21      男  理科一班\n9    1500100010  羿彦昌   23      男  理科六班\n10   1500100011  宰运华   21      男  理科三班\n12   1500100013  逯君昊   24      男  文科二班\n13   1500100014  羿旭炎   23      男  理科五班\n18   1500100019  娄曦之   24      男  理科三班\n19   1500100020  杭振凯   23      男  理科四班\n20   1500100021  连鸿晖   22      男  理科六班\n21   1500100022  薄运珧   23      男  文科四班\n22   1500100023  东鸿畴   23      男  理科二班\n24   1500100025  翁飞昂   22      男  文科四班\n25   1500100026  向鹏池   21      男  理科四班\n26   1500100027  路辰锟   21      男  文科四班\n27   1500100028  幸浩邈   24      男  理科五班\n28   1500100029  滕旭炎   21      男  理科二班\n30   1500100031  麻智刚   24      男  文科六班\n32   1500100033  桑昆峰   24      男  理科三班\n33   1500100034  薛鸿朗   24      男  理科五班\n34   1500100035  包瀚玥   24      男  理科四班\n35   1500100036  阮旭炎   22      男  文科二班\n37   1500100038  蓟振强   21      男  理科五班\n40   1500100041  傅景天   24      男  理科四班\n41   1500100042  麻旭尧   24      男  文科四班\n42   1500100043  晏昆鹏   24      男  理科六班\n44   1500100045  仇运晟   21      男  理科一班\n46   1500100047  秋旭尧   23      男  文科六班\n47   1500100048  从子辰   21      男  理科六班\n..          ...  ...  ...    ...   ...\n943  1500100944  查振国   22      男  理科四班\n947  1500100948  马昊天   24      男  理科二班\n950  1500100951  平彭泽   22      男  文科一班\n952  1500100953  戈昌茂   24      男  文科五班\n954  1500100955  逄德运   24      男  文科三班\n957  1500100958  柴铭晨   23      男  理科三班\n959  1500100960  乔旭尧   21      男  文科三班\n960  1500100961  李昂熙   24      男  文科四班\n961  1500100962  毕德明   23      男  理科二班\n963  1500100964  洪鸿骞   24      男  理科二班\n964  1500100965  尤昊伟   23      男  文科二班\n965  1500100966  庞昆雄   23      男  文科三班\n967  1500100968  谭晗日   24      男  文科五班\n968  1500100969  毛昆鹏   24      男  文科三班\n971  1500100972  王昂杰   23      男  理科二班\n973  1500100974  容鸿晖   21      男  文科五班\n974  1500100975  蓬曜瑞   22      男  理科三班\n977  1500100978  郜昆卉   21      男  文科五班\n978  1500100979  乐曜灿   24      男  文科六班\n980  1500100981  经鹏涛   23      男  文科六班\n983  1500100984  殷景逸   23      男  理科二班\n986  1500100987  双昆杰   24      男  文科四班\n987  1500100988  余鸿云   22      男  文科六班\n989  1500100990  扈旭鹏   23      男  理科三班\n990  1500100991  冉飞昂   22      男  理科一班\n991  1500100992  莫运盛   24      男  理科六班\n995  1500100996  厉运凡   24      男  文科三班\n996  1500100997  陶敬曦   21      男  理科六班\n997  1500100998  容昆宇   22      男  理科四班\n999  1500101000  符瑞渊   23      男  理科六班\n\n[507 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "print(student[student[\"gender\"]==\"男\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [],
   "source": [
    "student.to_csv(\"newstuednt.txt\", header=None, index=None, sep=\"\\t\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           name  age gender clazz\nid                               \n1500100001  施笑槐   22      女  文科六班\n1500100002  吕金鹏   24      男  文科六班\n1500100003  单乐蕊   22      女  理科六班\n1500100004  葛德曜   24      男  理科三班\n1500100005  宣谷芹   22      女  理科五班\n           name  age gender clazz\nid                               \n1500100996  厉运凡   24      男  文科三班\n1500100997  陶敬曦   21      男  理科六班\n1500100998  容昆宇   22      男  理科四班\n1500100999  钟绮晴   23      女  文科五班\n1500101000  符瑞渊   23      男  理科六班\nclazz\n文科一班     72\n文科三班     94\n文科二班     87\n文科五班     84\n文科六班    104\n文科四班     81\n理科一班     78\n理科三班     68\n理科二班     79\n理科五班     70\n理科六班     92\n理科四班     91\nName: clazz, dtype: int64\ngender\n女    22.501014\n男    22.540434\nName: age, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "#统计每个班级学生的人数\n",
    "print(student.head())\n",
    "print(student.tail())\n",
    "\n",
    "print(student[\"clazz\"].groupby(student[\"clazz\"]).count())\n",
    "\n",
    "print(student.groupby(student[\"gender\"])[\"age\"].agg(\"mean\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [],
   "source": [
    "score = pd.read_csv(\"com/shujia/score.txt\",header=None,names=[\"id\",\"c_id\",\"score\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           id     c_id  score\n0  1500100001  1000001     98\n1  1500100001  1000002      5\n2  1500100001  1000003    137\n3  1500100001  1000004     29\n4  1500100001  1000005     85\n"
     ]
    }
   ],
   "source": [
    "print(score.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           name  age gender clazz\nid                               \n1500100001  施笑槐   22      女  文科六班\n1500100002  吕金鹏   24      男  文科六班\n1500100003  单乐蕊   22      女  理科六班\n1500100004  葛德曜   24      男  理科三班\n1500100005  宣谷芹   22      女  理科五班\n               c_id  score\nid                        \n1500100001  1000001     98\n1500100001  1000002      5\n1500100001  1000003    137\n1500100001  1000004     29\n1500100001  1000005     85\n"
     ]
    }
   ],
   "source": [
    "#inplace  如果为true在原对象上修改，如果为false返回一个新的df\n",
    "student.set_index(\"id\", inplace=True)\n",
    "score.set_index(\"id\", inplace=True)\n",
    "print(student.head())\n",
    "print(score.head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           name  age gender clazz     c_id  score\nid                                               \n1500100001  施笑槐   22      女  文科六班  1000001     98\n1500100001  施笑槐   22      女  文科六班  1000002      5\n1500100001  施笑槐   22      女  文科六班  1000003    137\n1500100001  施笑槐   22      女  文科六班  1000004     29\n1500100001  施笑槐   22      女  文科六班  1000005     85\n"
     ]
    }
   ],
   "source": [
    "ssdf = student.join(score,on=\"id\",how=\"left\")\n",
    "print(ssdf.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id\n1500100001    406\n1500100002    440\n1500100003    359\n1500100004    421\n1500100005    395\nName: score, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#统计每个学生的总分\n",
    "sumdf = ssdf.groupby(by=\"id\")[\"score\"].sum()\n",
    "print(sumdf.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           name  age gender clazz  score\nid                                      \n1500100001  施笑槐   22      女  文科六班    406\n1500100002  吕金鹏   24      男  文科六班    440\n1500100003  单乐蕊   22      女  理科六班    359\n1500100004  葛德曜   24      男  理科三班    421\n1500100005  宣谷芹   22      女  理科五班    395\n"
     ]
    }
   ],
   "source": [
    "sumssdf = student.join(sumdf,on=\"id\")\n",
    "print(sumssdf.head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "clazz\n文科一班    628\n文科三班    568\n文科二班    611\n文科五班    589\n文科六班    583\n文科四班    612\n理科一班    520\n理科三班    630\n理科二班    586\n理科五班    628\n理科六班    587\n理科四班    534\nName: score, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "\n",
    "#统计每个班级分数最高的学生\n",
    "maxdf = pd.DataFrame(sumssdf.groupby(sumssdf[\"clazz\"])[\"score\"].agg(\"max\"))\n",
    "print(maxdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  name  age gender clazz  score_x  score_y\n0  施笑槐   22      女  文科六班      406      583\n1  吕金鹏   24      男  文科六班      440      583\n2  尚孤风   23      女  文科六班      418      583\n3  骆怜雪   21      女  文科六班      425      583\n4  麻智刚   24      男  文科六班      209      583\n"
     ]
    }
   ],
   "source": [
    "joinDf = sumssdf.merge(maxdf,on=\"clazz\")\n",
    "print(joinDf.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    name  age gender clazz  score_x  score_y\n11   黎昆鹏   22      男  文科六班      583      583\n164  田晨潍   21      男  理科六班      587      587\n260  满慕易   21      女  理科三班      630      630\n272  巫景彰   21      男  理科五班      628      628\n397  谷念薇   21      女  理科二班      586      586\n429  沈香巧   24      女  理科一班      520      520\n523  蓟海昌   22      男  文科二班      611      611\n597  黄初夏   23      女  文科一班      628      628\n712  蓬怀绿   23      女  理科四班      534      534\n813  路鸿志   24      男  文科四班      612      612\n897  闻运凯   24      男  文科五班      589      589\n913  云冰真   24      女  文科三班      568      568\n"
     ]
    }
   ],
   "source": [
    "print(joinDf[joinDf[\"score_x\"]==joinDf[\"score_y\"]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
